#!/bin/bash
# ods_to_dwd.sh 表名|all do_date 

if [ -n "$2" ]
then
	do_date=$2
else
	do_date=`date -d "-1 day" +%F`
fi

dwd_interaction_plays_inc="
insert overwrite table dwd_interaction_plays_inc partition(dt='${do_date}')
select
    common.uid,
    common.ar,
    common.ba,
    common.ch,
    common.is_new,
    common.md,
    common.mid,
    common.os,
    common.sc,
    common.sid,
    common.vc,
    appvideo.play_sec,
    appvideo.position_sec,
    appvideo.video_id,
    ts,
    from_unixtime(cast(ts/1000 as bigint),'yyyy-MM-dd') date_id
from ods_log_inc
where dt = '${do_date}' and appvideo.video_id is not null;
"

dwd_interaction_review_inc="
insert overwrite table dwd_interaction_review_inc partition(dt='${do_date}')
select
    data.id,
    data.user_id,
    data.course_id,
    data.review_txt,
    data.review_stars,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time,
    data.deleted
from ods_review_info_inc
where dt='${do_date}' and type='insert';
"

dwd_trade_order_detail_inc="
insert overwrite table dwd_trade_order_detail_inc partition (dt = '${do_date}')
select
    order_detail.id,
    order_id,
    order_detail.user_id,
    course_id,
    course_name,
    origin_amount,
    coupon_reduce,
    final_amount,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    order_info.session_id,
    province_id,
    source_id,
    source_site,
    source_url
from
    (
        select data.id            id,
               data.course_id     course_id,
               data.course_name   course_name,
               data.order_id      order_id,
               data.user_id       user_id,
               data.origin_amount origin_amount,
               data.coupon_reduce coupon_reduce,
               data.final_amount  final_amount,
               data.create_time   create_time
        from ods_order_detail_inc
        where type = 'insert' and dt = '${do_date}'
    ) order_detail left join
    (
        select
            data.id id,
            data.province_id,
            data.session_id
        from ods_order_info_inc
        where type = 'insert' and dt = '2022-02-22'
    ) order_info on order_detail.order_id = order_info.id

                   left join
    (
        select

            page.page_id page_id,
            common.sid session_id,
            common.uid user_id,
            common.sc source_id
        from ods_log_inc
        where dt = '${do_date}' and page.page_id = 'order'
    ) log on order_info.session_id = log.session_id
                   left join
    (
        select
            id,
            source_site,
            source_url
        from ods_base_source_full
        where dt = '${do_date}'
    ) sr on sr.id = log.source_id;
"

dwd_trade_cart_add_inc="
insert overwrite table dwd_trade_cart_add_inc partition (dt = '${do_date}' )
select
    id,
    user_id,
    course_id,
    course_name,
    cart_price,
    session_id,
    date_format(create_time, 'yyyy-MM-dd') create_time,
    deleted,
    sold
from
    (
        select
            data.id,
            data.user_id,
            data.course_id,
            data.course_name,
            data.cart_price,
            data.session_id,
            data.create_time,
            data.deleted,
            data.sold
        from ods_cart_info_inc
        where dt = '${do_date}' and type = 'insert'
    )t1;
"

dwd_study_complete_play_inc="
with process as (
    select data.id,
           data.course_id,
           data.chapter_id,
           data.user_id,
           data.position_sec play_duration,
           data.create_time
    from ods_user_chapter_process_inc
    where dt = '${do_date}'
      and type = 'insert'
),
     video as (
         select id,
                video_name,
                during_sec video_duration,
                chapter_id,
                chapter_name
         from dim_video_full
         where dt = '${do_date}'
     ),
     course as (
         select id,
                course_name,
                chapter_num
         from dim_course_full
         where dt = '${do_date}'
     )
insert overwrite table dwd_study_complete_play_inc partition(dt='${do_date}')
select process.id,
       user_id,
       video.id,
       video_name,
       process.chapter_id,
       chapter_name,
       course_id,
       course_name,
       chapter_num,
       date_format(create_time, 'yyyy-MM-dd')          date_id,
       date_format(create_time, 'yyyy-MM-dd HH:mm:ss') play_time
from process
         left join video on video.chapter_id = process.chapter_id
         left join course on course.id = process.course_id
where play_duration / video_duration >= 0.9;
"

dwd_study_play_video_inc="
with process as (
    select data.id,
           data.course_id,
           data.chapter_id,
           data.user_id,
           data.position_sec play_duration,
           data.create_time
    from ods_user_chapter_process_inc
    where dt = '${do_date}'
      and type = 'insert'
),
     video as (
         select id,
                video_name,
                during_sec video_duration,
                chapter_id,
                chapter_name
         from dim_video_full
         where dt = '${do_date}'
     ),
     course as (
         select id,
                course_name
         from dim_course_full
         where dt = '${do_date}'
     )
insert overwrite table dwd_study_play_video_inc partition (dt = '${do_date}')
select process.id,
       user_id,
       course_id,
       course_name,
       process.chapter_id,
       chapter_name,
       video.id,
       video_name,
       video_duration,
       date_format(create_time, 'yyyy-MM-dd')          date_id,
       date_format(create_time, 'yyyy-MM-dd HH:mm:ss') play_time,
       play_duration
from process
         left join video on video.chapter_id = process.chapter_id
         left join course on course.id = process.course_id;
"

dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table dwd_traffic_page_view_inc partition (dt='${do_date}')
select
    common.ar,
    common.ba,
    common.ch,
    common.is_new,
    common.md,
    common.mid,
    common.os,
    common.sc,
    common.sid,
    common.uid,
    page.during_time,
    page.item,
    page.item_type,
    page.last_page_id,
    page.page_id,
    page.source_type,
    date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'Asia/Shanghai'),'yyyy-MM-dd HH:mm:ss') view_time
from ods_log_inc where dt = '${do_date}' and page.page_id is not null;
"

dwd_user_register_inc="
insert overwrite table dwd_user_register_inc partition (dt = '${do_date}')
select
    data.id,
    data.login_name,
    data.nick_name,
    data.real_name,
    data.create_time,
    date_format(data.create_time, 'yyyy-MM-dd') data_id
from ods_user_info_inc
where dt = '${do_date}' and type = 'insert';
"

dwd_user_login_inc="
insert overwrite table dwd_user_login_inc partition (dt = '${do_date}')
select
    user_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    source_id,
    source_site,
    source_url,
    session_id,
    is_new,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from (
         select
             common.uid user_id,
             ts,
             common.ar province_id,
             common.ba brand,
             common.ch channel,
             common.is_new is_new,
             common.os operate_system,
             common.sid session_id,
             common.vc version_code,
             common.mid mid_id,
             common.md model,
             common.sc source_id
         from ods_log_inc
         where dt = '${do_date}' and page.page_id = 'mine'
     ) t1 left join (
    select
        id,
        source_site,
        source_url
    from ods_base_source_full
    where dt = '${do_date}'
) t2 on t1.source_id = t2.id;
"

dwd_test_test_exam_inc="
insert overwrite table dwd_test_test_exam_inc partition (dt='${do_date}')
    select
        data.id,
        data.user_id,
        data.paper_id,
        data.score,
        data.duration_sec,
        date_format(data.create_time,'yyyy-MM-dd') date_id,
        data.create_time,
        data.submit_time
    from ods_test_exam_inc
    where dt = '${do_date}'
    and  type = 'insert' and type ='update';
"

dwd_test_question_test_exam_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
with A as(
    select
        data.id,
        data.user_id,
        data.paper_id,
        data.question_id,
        data.answer,
        data.score,
        data.is_correct,
        date_format(data.create_time,'yyyy-MM-dd') date_id
    from ods_test_exam_question_inc
    where dt = '${do_date}'
      and  type = 'insert' and type= 'update'
),
     B as (
         select
             id question_id,
             question_txt
         from  ods_test_question_info_full
         where dt = '${do_date}'
     )
insert overwrite table dwd_test_question_test_exam_inc partition (dt='${do_date}')
select
    id,
    user_id,
    paper_id,
    A.question_id,
    question_txt,
    answer,
    score,
    is_correct,
    date_id
from A left join B
 on A.question_id=B.question_id;
"


case $1 in
"dwd_interaction_plays_inc")
	hive -e "use edu;${dwd_interaction_plays_inc}"
;;
"dwd_interaction_review_inc")
	hive -e "use edu;${dwd_interaction_review_inc}"
;;
"dwd_trade_order_detail_inc")
	hive -e "use edu;${dwd_trade_order_detail_inc}"
;;
"dwd_trade_cart_add_inc")
	hive -e "use edu;${dwd_trade_cart_add_inc}"
;;
"dwd_study_complete_play_inc")
	hive -e "use edu;${dwd_study_complete_play_inc}"
;;
"dwd_study_play_video_inc")
	hive -e "use edu;${dwd_study_play_video_inc}"
;;
"dwd_test_test_exam_inc")
	hive -e "use edu;${dwd_test_test_exam_inc}"
;;
"dwd_test_question_test_exam_inc")
	hive -e "use edu;${dwd_test_question_test_exam_inc}"
;;
"dwd_traffic_page_view_inc")
	hive -e "use edu;${dwd_traffic_page_view_inc}"
;;
"dwd_user_register_inc")
	hive -e "use edu;${dwd_user_register_inc}"
;;
"dwd_user_login_inc")
	hive -e "use edu;${dwd_user_login_inc}"
;;
"all")
	hive -e "use edu;${dwd_interaction_plays_inc}${dwd_interaction_review_inc}${dwd_trade_order_detail_inc}${dwd_trade_cart_add_inc}${dwd_study_complete_play_inc}${dwd_study_play_video_inc}${dwd_test_test_exam_inc}${dwd_test_question_test_exam_inc}${dwd_traffic_page_view_inc}${dwd_user_register_inc}${dwd_user_login_inc}"
;;
esac
